﻿Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient


Imports System.Runtime.InteropServices.Marshal
Imports System.Web
Imports System.Security.Principal
Imports System.Runtime.InteropServices
Imports System.Configuration
Imports System
Partial Public Class Wfrm_SubeExcel
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            ViewState("usu") = Session("usuario")
            CreaDatatable()
            pnl_mensaje0.Visible = False
            pnl_mensaje0.UpdateAfterCallBack = True
            Carga_Datos_Usuario()
        End If
    End Sub
    Sub Carga_Datos_Usuario()        
        Dim exec As New DotNetDal.SQLPersistence(False, "cnn", DotNetDal.SQLPersistence.Provider.SQLCLIENT)
        Dim rp As New DotNetResponse.SQLPersistence
        rp.StoredProcedure = "SP_OC_Get_INFO_FORM"
        rp.AddParametro(ViewState("usu"))
        exec.ExecuteDataTable(rp)

        If rp.TieneDatos Then
            lbl_clinica.Text = rp.DtTable.Rows(0).Item("tNombreFantasia").ToString.Trim
            lbl_usuario.Text = rp.DtTable.Rows(0).Item("Tnombre").ToString.Trim
            lbl_fecha.Text = Now

            lbl_clinica.UpdateAfterCallBack = True
            lbl_usuario.UpdateAfterCallBack = True
            lbl_fecha.UpdateAfterCallBack = True

        End If

    End Sub
    Protected Sub btnProcesar_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnProcesar.Click
        '  Try


        Dim exec As New DotNetDal.SQLPersistence(True, "cnn", DotNetDal.SQLPersistence.Provider.SQLCLIENT)
        Dim rp As New DotNetResponse.SQLPersistence

        rp.StoredProcedure = "SP_OC_DELETE_EXCEL"
        rp.AddParametro(ViewState("usu"))
        exec.ExecuteNonQuery(rp)
        exec.Commit()
        If rp.Errores Then
        Else
            rp.ClearParametro()
            rp.TieneDatos = False
        End If



        Dim Rutinas As New DataServices.Rutinas
        Dim Destino As String

        If IsNothing(myfile.PostedFile) Then
            lblMensaje("Seleccione un archivo")
            Exit Sub
        End If

        If myfile.PostedFile.ContentLength = 0 Then
            lblMensaje("No se cargó, archivo vacío ")
            Exit Sub
        End If

        Dim nombreF As String = Path.GetFileName(myfile.PostedFile.FileName)
        Dim nFile As String = Mid(nombreF, 1, Len(nombreF) - 4)
        Dim tFile As String = Mid(nombreF, Len(nombreF) - 3)
        If UCase(tFile) <> "XLS" Then
            If UCase(tFile) <> "XLSX" Then
                lblMensaje("Tipo del archivo errado. Debe ser .XLS")
                'scriptblock1 = "<script language='javascript'>" & Destino & "</script>"
                'Page.RegisterStartupScript("Launch_Report_Window", scriptblock1)
                Exit Sub
            End If

        End If
        Dim currentTime As System.DateTime = System.DateTime.Now
        Dim dfecha_1 As String = "_" & currentTime.Year & currentTime.Month & currentTime.Day & "_" & currentTime.Hour & currentTime.Minute & currentTime.Second
        Destino = Server.MapPath(Nothing) + "\Upload\" + nFile + dfecha_1 + tFile


        Dim excelSheetNames As String()

        myfile.PostedFile.SaveAs(Destino)

        excelSheetNames = GetExcelSheetNames(Destino)

        excelToSql(Destino, excelSheetNames(0), "TBL_OC_UPLOAD_SOL")

        carga_grilla()

        Pnl_Proceso.Visible = True
        Pnl_Proceso.UpdateAfterCallBack = True

        'Catch ex As Exception
        '    lblMensaje("ARCHIVO INVALIDO : \n" & ex.Message.Replace("'", "").Replace("<", "").Replace(">", ""))
        'End Try

    End Sub
    Function excelToSql(ByVal FileName As String, ByVal SheetName As String, ByVal TableName As String) As String
        'excelToSql = ""

        'Dim Rutinas As New DataServices.Rutinas
        'Dim j As Integer
        'Dim mensajeBD As String
        'Dim scriptblock1 As String
        'Dim destino As String
        'Dim Dias As Integer
        'xglosa.Text = ""

        'Try


        '    Dim LISTA_BD As String
        '    LISTA_BD = "RUT,VENDEDOR"

        Dim SQL As String = ConfigurationManager.AppSettings("cnn")
        Dim cmdDatabase As SqlCommand
        Dim contoSql As SqlConnection
        Dim contoExcel As OleDbConnection
        Dim cmd As OleDbCommand
        Dim Excel As String = " Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & FileName & ";Extended Properties=""Excel 12.0;HDR=YES"";"

        contoExcel = New OleDbConnection(Excel)
        contoExcel.Open()
        '    mensajeBD = ""
        cmd = New OleDbCommand("SELECT [" & SheetName.ToString & "].*, '" & ViewState("usu") & "' as usuario,'ok' as estado FROM [" & SheetName.ToString & "] WHERE BDG_CODIGO IS NOT NULL", contoExcel)
        Dim rdr As OleDbDataReader = cmd.ExecuteReader()
        Dim schema As DataTable = rdr.GetSchemaTable
        '    Dim name As String = ""

        '    Dim Lista_Col As String = ""
        '    For Each row As DataRow In schema.Rows
        '        Dim i As Integer = 0
        '        For Each col As DataColumn In schema.Columns

        '            If i = 0 Then
        '                Lista_Col = Lista_Col & Convert.ToString(row(0)) & ","
        '                name = name & "[" & Convert.ToString(row(0)) & "] nvarchar(255), "
        '            End If
        '            i = i + 1
        '        Next
        '    Next

        '    name = Mid(name, 1, Len(name) - 2)
        '    Lista_Col = Mid(Lista_Col, 1, Len(Lista_Col) - 1)
        '    Dim LISTA_BD_r() As String = LISTA_BD.Split(",")
        '    Dim Lista_Col_r() As String = Lista_Col.Split(",")

        '    Dim k As Integer
        '    Dim esta As Integer = 0
        '    Dim tit_aux As String = ""
        '    Dim mensaje As String = ""

        '    For k = 0 To UBound(LISTA_BD_r)
        '        tit_aux = LISTA_BD_r(k)
        '        For j = 0 To UBound(Lista_Col_r)
        '            If UCase(LISTA_BD_r(k)) = UCase(Lista_Col_r(j)) Then
        '                tit_aux = ""
        '            End If
        '        Next
        '        If tit_aux <> "" Then
        '            mensaje = mensaje & " , " & tit_aux
        '        End If
        '    Next

        '    If mensaje <> "" Then
        '        contoExcel.Close()
        '        mensaje = "LAS SIGUIENTES COLUMNAS FALTAN O EL NOMBRE EN LA PLANILLA ES INCORRECTO : " & mensaje
        '        destino = "alert('" & mensaje & "');"
        '        scriptblock1 = "<script language='javascript'>" & destino & "</script>"
        '        Page.RegisterStartupScript("Launch_Report_Window", scriptblock1)
        '        contoExcel.Close()
        '        contoExcel.Dispose()
        '        Exit Function

        '    End If

        '    Dim comandoSql As String
        '    comandoSql = "IF object_id('" & TableName & "') IS NOT NULL BEGIN    DROP TABLE " & TableName & " END "
        '    comandoSql = comandoSql & "Create table " & TableName & " (" & name & ")"
        contoSql = New SqlConnection(SQL)
        contoSql.Open()
        '    cmdDatabase.ExecuteNonQuery()

        Using bulkCopy As New SqlBulkCopy(contoSql)
            bulkCopy.DestinationTableName = TableName
            bulkCopy.BulkCopyTimeout = 60
            bulkCopy.WriteToServer(rdr)
        End Using

        '    Dim Csql As String

        '    Csql = " "
        '    Csql = "update UploadRutConvenio set rut=REPLACE(LTRIM(REPLACE(rut, '0', ' ')), ' ', '0') "
        '    Csql = Csql & "update UploadRutConvenio set rut=replace(rut,'.','') "
        '    Csql = Csql & " update UploadRutConvenio set rut=replace(rut,',','') "
        '    Csql = Csql & " update UploadRutConvenio set rut=upper(rut) "

        '    Rutinas.EjecutarSql(Csql)



        '    Csql = "select vendedor as campo1 from UploadRutConvenio where vendedor not in (select tcodigo_pk from vendedores where cestado=1 )"

        '    If Busca(Csql, "Código Vendedor. ") = 1 Then
        '        xglosa.Text = "No existe Vendedor . Proceso cancelado "
        '        excelToSql = xglosa.Text
        '        contoExcel.Close()
        '        contoExcel.Dispose()
        '        Exit Function
        '    End If

        '    grilla.Visible = False


        '    Csql = " Select [RUT] + '-->' + convert(varchar(10),vendedor) as campo1 from UploadRutConvenio where DBO.VALIDADV([RUT])=1 "

        '    If Busca(Csql, "RUT Invalidos") = 1 Then
        '        xglosa.Text = "En la planilla hay  RUT no validos . Los RUT invalidos fueron excluidos del proceso.  "
        '        excelToSql = xglosa.Text
        '        grilla.Visible = True
        '        'contoExcel.Close()
        '        'contoExcel.Dispose()
        '        'Exit Function
        '    End If




        '    If SP = 1 Then
        '        Dim Rp As New gsonorad.ResponseValidator("", "")
        '        Rp.ExecuteDataset("SP_" & TableName, Nothing)

        '        If Not Rp.Errores Then
        '            Rp.Commit()
        '        Else
        '            mensajeBD = Rp.Mensaje
        '        End If
        '        Rp.Dispose()
        '    End If

        contoSql.Close()
        contoExcel.Close()
        contoExcel.Dispose()
        '    If mensajeBD = "" Then
        '        excelToSql = "Datos Transferidos"
        '    Else
        '        excelToSql = mensajeBD
        '    End If
        'Catch ex As Exception
        '    excelToSql = ex.Message
        'End Try

    End Function

    Public Function GetExcelSheetNames(ByVal excelFileName As String) As String()
        Dim con As OleDbConnection = Nothing
        Dim dt As DataTable = Nothing
        Dim conStr As String = (" Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & excelFileName & ";Extended Properties=""Excel 12.0;HDR=YES"";")

        con = New OleDbConnection(conStr)
        con.Open()
        dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
        If dt Is Nothing Then
            Return Nothing
        End If
        Dim excelSheetNames As String() = New String(dt.Rows.Count - 1) {}
        Dim i As Integer = 0
        For Each row As DataRow In dt.Rows
            excelSheetNames(i) = row("TABLE_NAME").ToString()
            i += 1
        Next
        con.Close()
        con.Dispose()
        Return excelSheetNames
    End Function

    Sub carga_grilla()
        Dim exec As New DotNetDal.SQLPersistence(False, "cnn", DotNetDal.SQLPersistence.Provider.SQLCLIENT)
        Dim rp As New DotNetResponse.SQLPersistence
        Dim erro As Boolean = False
        lbl_rechazados.Text = 0

        rp.StoredProcedure = "SP_OC_PRODUCTOS_BODEGA_NOK"
        rp.AddParametro(ViewState("usu"))
        exec.ExecuteDataTable(rp)
        If rp.TieneDatos Then
            lbl_rechazados.Text = rp.DtTable.Rows.Count
            grdNoDisponible.DataSource = rp.DtTable
            grdNoDisponible.DataBind()
            grdNoDisponible.UpdateAfterCallBack = True


            'SE BLOQUEA BOTON DE PROCESO, SOLICITADO POR MB

            btnGrabaSol.Visible = False
            btnGrabaSol.UpdateAfterCallBack = True

            'Mensaje 23 julio 2011
            lblMensaje("planilla no procesada por datos inválidos")
            erro = True
        End If


        rp.ClearParametro()
        rp.TieneDatos = False
        rp.StoredProcedure = "SP_OC_PRODUCTOS_BODEGA_OK"
        rp.AddParametro(ViewState("usu").ToString.Trim)
        exec.ExecuteDataTable(rp)


        lbl_aceptados.Text = 0

        If rp.TieneDatos Then
            lbl_aceptados.Text = rp.DtTable.Rows.Count
            grdDisponible.DataSource = rp.DtTable
            grdDisponible.DataBind()
            grdDisponible.UpdateAfterCallBack = True
            If Not erro Then
                lblMensaje("Wfrm_SubeExcel", "MensajeLBL")
            End If

        End If


        lbl_cargados.Text = Val(lbl_aceptados.Text) + Val(lbl_rechazados.Text)
        lbl_cargados.UpdateAfterCallBack = True

        lbl_aceptados.UpdateAfterCallBack = True
        lbl_rechazados.UpdateAfterCallBack = True

    End Sub

    Protected Sub btnGrabaSol_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnGrabaSol.Click
        Dim exec As New DotNetDal.SQLPersistence(True, "cnn", DotNetDal.SQLPersistence.Provider.SQLCLIENT)
        Dim rp As New DotNetResponse.SQLPersistence
        rp.StoredProcedure = "SP_OC_DISTINCT_BODEGA"

        If grdDisponible.Items.Count = 0 Then
            pnl_mensaje0.Visible = True
            pnl_mensaje0.UpdateAfterCallBack = True
            lbl_mensaje0.Text = "Ingrese Productos Disponibles"
            lbl_mensaje0.UpdateAfterCallBack = True
            Exit Sub
        Else
            pnl_mensaje0.Visible = False
            pnl_mensaje0.UpdateAfterCallBack = True
        End If

        exec.ExecuteDataTable(rp)

        If rp.TieneDatos Then
            For i = 0 To rp.DtTable.Rows.Count - 1
                Dim cd As Integer = rp.DtTable.Rows(i).Item("ID_BODEGA").ToString
                Dim rp0 As New DotNetResponse.SQLPersistence
                rp0.ClearParametro()
                rp0.TieneDatos = False
                rp0.StoredProcedure = "SP_OC_INSERT_SOLICITUD"
                rp0.AddParametro(Session("empresa")) '("FK_EMPRESA") 'nClinicaID de usuario, HACER SP
                rp0.AddParametro(cd) '("FK_BODEGA") 'id_bodega del combobox
                rp0.AddParametro(ViewState("usu")) '("FK_USUARIO_CREA") 
                rp0.AddParametro("0")
                rp0.AddParametro("")
                rp0.AddParametro("0")
                rp0.AddParametro("0")
                exec.ExecuteScalar(rp0) 'cuando queremos que devuelva un valor, el @@IDENTITY del SP.

                ' @FK_EMPRESA INT  
                ',@FK_BODEGA NUMERIC (18,0)  
                ',@FK_USUARIO_CREA VARCHAR (30)  
                ',@ESTADO_SOL INT  

                Dim id_sol As String
                id_sol = rp0.GetScalar


                If Not rp0.Errores Then
                    CargarSolicitudes(id_sol, rp.DtTable.Rows(i).Item("BDG_NOMBRE").ToString)
                End If

                rp0.ClearParametro()
                rp0.TieneDatos = False
                rp0.StoredProcedure = "SP_OC_INSERT_PROD_OK"
                rp0.AddParametro(id_sol)
                rp0.AddParametro(ViewState("usu"))
                rp0.AddParametro(cd)
                exec.ExecuteNonQuery(rp0)

                If rp0.Errores Then
                    rp.Errores = True
                    rp.MensajeError = rp0.MensajeError
                End If
            Next
        End If

        If rp.Errores Then
            lblMensaje(rp.MensajeError)
            exec.Rollback()
        Else
            Mensaje0("Wfrm_SubeExcel", "OkExcel")
            rp.ClearParametro()
            rp.TieneDatos = False
            rp.StoredProcedure = "SP_OC_DELETE_EXCEL"
            rp.AddParametro(ViewState("usu"))
            exec.ExecuteNonQuery(rp)
            exec.Commit()


            Grid_Generadas.DataSource = ViewState("solicitudes")
            Grid_Generadas.DataBind()
            Grid_Generadas.UpdateAfterCallBack = True

        End If
    End Sub
    Sub CargarSolicitudes(ByVal Id_Solicitud As String, ByVal id_bodega As String)
        'Grid_Generadas
        If ViewState("solicitudes") Is Nothing Then
            Dim dt As New DataTable
            dt.Columns.Add("id_solicitud")
            dt.Columns.Add("BDG_NOMBRE")
            Dim dr As DataRow
            dr = dt.NewRow
            dr(0) = Id_Solicitud
            dr(1) = id_bodega
            dt.Rows.Add(dr)
            ViewState("solicitudes") = dt
        Else
            Dim dr As DataRow
            dr = ViewState("solicitudes").NewRow
            dr(0) = Id_Solicitud
            dr(1) = id_bodega
            ViewState("solicitudes").Rows.Add(dr)
        End If

    End Sub
    Sub CreaDatatable()
        Dim Dt2 As New DataTable
        Dt2.Columns.Add("Producto")
        Dt2.Columns.Add("Bodega")
        Dt2.Columns.Add("Estado")
        Dt2.Columns.Add("BDG_CODIGO")
        Dt2.Columns.Add("FK_EMPRESA")
        Dt2.Columns.Add("ID_BODEGA")
        Dt2.Columns.Add("AUD_USUARIO")
        Dt2.Columns.Add("PRO_CODIGO")
        Dt2.Columns.Add("DOC_CANTIDAD")

        ViewState("DT") = Dt2
    End Sub

    Sub Mensaje0(ByVal tbl As String, ByVal codigo As String)

        Dim mensaje As String = FuncionesGlobal.GetMensaje(tbl, codigo)
      


        If Anthem.Manager.IsCallBack Then
            Anthem.Manager.AddScriptForClientSideEval("alert('" & Mensaje & "')")
        Else
            Page.ClientScript.RegisterStartupScript(Me.GetType, "alertar", "<script>alert('" & Mensaje & "')</script>")
        End If



    End Sub
    Sub lblMensaje(ByVal tbl As String, ByVal codigo As String)

        Dim Mensaje As String = FuncionesGlobal.GetMensaje(tbl, codigo)

        If Anthem.Manager.IsCallBack Then
            Anthem.Manager.AddScriptForClientSideEval("alert('" & Mensaje & "')")
        Else
            Page.ClientScript.RegisterStartupScript(Me.GetType, "alertar", "<script>alert('" & Mensaje & "')</script>")
        End If

    End Sub

    Sub lblMensaje(ByVal Mensaje As String)
        If Anthem.Manager.IsCallBack Then
            Anthem.Manager.AddScriptForClientSideEval("alert('" & Mensaje & "')")
        Else
            Page.ClientScript.RegisterStartupScript(Me.GetType, "alertar", "<script>alert('" & Mensaje & "')</script>")
        End If
    End Sub

End Class